# -*- coding: UTF8 -*-
import os
import sqlite3
import shutil
import uuid
import sys, time

import win32serviceutil 
import win32service 
import win32event
import winerror
import servicemanager
# win32timezone为用pyinstaller打包时需要
import win32timezone


# 检查或创建【个人设置】数据库和表
def checkOrCreateDB(db_name):
    conn = sqlite3.connect(db_name)
    conn.execute('''
        CREATE TABLE IF NOT EXISTS FolderInformation
        (ID INTEGER PRIMARY KEY AUTOINCREMENT,
        macAddress char(12) NOT NULL,
        zoteroStorage varchar(256) NOT NULL,
        cloudDir varchar(256) NOT NULL
        );
    ''')

    conn.commit()
    conn.close()

# 获取当前计算机的mac地址
def getMacAddress():
    node = uuid.getnode()
    mac = uuid.UUID(int = node).hex[-12:]
    return mac

# 从数据库种获取当前PC的storage和cloud目录信息
def fetchCurrentMacAddress(db_filename):
    if not os.path.exists(db_filename):
        return None

    conn = sqlite3.connect(db_filename)
    c = conn.cursor()
    t = (getMacAddress(),)
    c.execute('SELECT * FROM FolderInformation where macAddress=?',t)
    result = c.fetchone()
    conn.close()
    return result

# 插入storage和cloud目录信息到数据库
def insertCurrentFolderInfo(db_filename, zotero_storage, cloud_dir):
    conn = sqlite3.connect(db_filename)
    c = conn.cursor()
    t = (getMacAddress(), zotero_storage, cloud_dir)
    c.execute('INSERT INTO FolderInformation VALUES (NULL,?,?,?)', t)
    conn.commit()
    conn.close()


#------------------------------------------------------
# -----------------------------------------------------
# 查询所有应该保留下来的附件
def fetchAllKeepedAttachments(zotero_sqlite_path):
    if not os.path.exists(zotero_sqlite_path):
        return None

    conn = sqlite3.connect(zotero_sqlite_path)
    c = conn.cursor()
    c.execute('SELECT [key], [value] FROM ShouldKeepedAttachments')
    result_list = c.fetchall()
    conn.close()
    return result_list

# 检查或在【zotero.sqlie】数据库中创建视图
def checkOrCreateViews(zoterosqlite_filename):
    if not os.path.exists(zoterosqlite_filename):
        return
    conn = sqlite3.connect(zoterosqlite_filename)
    try:
        # 视图：查询标记为被删除的Item
        conn.execute('''
            Create View if not exists RemovedAttachments
            AS
            SELECT [itemData].[itemID]
                ,[items].[key]
                ,[value]
                ,[dateDeleted]
            FROM [itemData]
                ,[itemDataValues]
                ,[deletedItems]
                ,[items]
            where [itemData].valueID = [itemDataValues].valueID
                and [itemData].itemID = [deletedItems].itemID
                and [itemData].itemID = [items].itemID
                and (
                    [value] like '%.pdf'
                    or [value] like '%.docx'
                    or [value] like '%.doc'
                    or [value] like '%.txt'
                    or [value] like '%.md'
                    or [value] like '%.caj'
                    )
        ''')

        # 视图：查询查询所有附件
        conn.execute('''
            Create View if not exists AllAttachments
            AS
            SELECT [items].[itemID]
                ,[key]
                ,[itemTypes].typeName
                ,[value]
            FROM [items]
                ,[itemTypes]
                ,[itemData]
                ,[itemDataValues]
            where [items].itemTypeID = [itemTypes].itemTypeID
                and [itemData].itemID = [items].itemID
                and [itemData].valueID = [itemDataValues].valueID
                and [itemTypes].typeName = 'attachment'
                and (
                    [value] like '%.pdf'
                    or [value] like '%.docx'
                    or [value] like '%.doc'
                    or [value] like '%.txt'
                    or [value] like '%.md'
                    or [value] like '%.caj'
                    );
        ''')

        # 视图：应该被保留下来的附件
        conn.execute('''
            Create View if not exists ShouldKeepedAttachments
            AS
            select [itemID]
                ,[key]
                ,[typeName]
                ,[value]
            from AllAttachments
            where [key] not in (
                    select [key]
                    from RemovedAttachments
                    );
        ''')

        conn.commit()
    except sqlite3.OperationalError as e:
        print(e.args)
    finally:
        conn.close()

# 列出云同步目录中的所有文件
def listAllNutsFiles(nuts_dir, fileFilter = None):
    doc_list = []
    # 后缀名过滤列表，即只保留文件扩展名为的pdf、docx等文件
    if fileFilter is None:
        fileFilter = ['.pdf', '.docx', '.doc', '.txt', '.caj', '.md']
    if not os.path.isdir(nuts_dir):
        return None

    subdirs_and_files = os.listdir(nuts_dir)
    file_lst  = []
    for ele in subdirs_and_files:
        if os.path.isfile(os.path.join(nuts_dir, ele)):
            file_lst.append(ele)
    #过滤文件
    filtered_files = [f for f in file_lst if os.path.splitext(f)[1] in fileFilter]
    return filtered_files

# 比较两个文件first和second，以较新的文件覆盖另一个文件
def copyNewEachOther(first, second):
    if not first or not second:
        return None
    if not os.path.isfile(first) or not os.path.isfile(second):
        return None

    first_time = os.path.getmtime(first)
    second_time = os.path.getmtime(second)
    try:
        if first_time> second_time:
            shutil.copy2(first,os.path.dirname(second))
        elif second_time> first_time:
            shutil.copy2(second,os.path.dirname(first)) 
        return True
    except IOError as e:
        print(e.args)
    
# storage目录和cloud目录下的文件互相拷贝
def interleavedCopy(zotero_storage, cloud_dir, keeped_fileinfo_lst):

    if keeped_fileinfo_lst is None or 0==len(keeped_fileinfo_lst):
        return None
    if not os.path.isdir(zotero_storage) or not os.path.isdir(cloud_dir):
        return None
    
    for k_dir, fname in keeped_fileinfo_lst:
        storage_filepath = os.path.join(zotero_storage, k_dir, fname)
        clouddir_filepath = os.path.join(cloud_dir, fname)
        storagefile_isExists = os.path.exists(storage_filepath)
        cloudfile_isExists = os.path.exists(clouddir_filepath)
 
        try:
            # 1. storage文件存在，而云同步目录文件不存在，直接复制到云同步目录
            if storagefile_isExists and (False==cloudfile_isExists):
                shutil.copy2(storage_filepath,cloud_dir)
            elif (False==storagefile_isExists) and cloudfile_isExists:
                # 2. storage文件不存在，而云同步目录文件存在，则复制到storage目录下
                # 先看storage目录下的key值（8位数字和字母）目录是否存在，不存在则创建
                k_dir_path = os.path.join(zotero_storage, k_dir)
                if not os.path.exists(k_dir_path):
                    os.mkdir(k_dir_path)
                shutil.copy2(clouddir_filepath, k_dir_path)
            elif storagefile_isExists and cloudfile_isExists:
                # 3.storage目录和云同步目录下文件都存在，则比较修改时间。以modify时间新的文件为准
                copyNewEachOther(storage_filepath, clouddir_filepath)
        except IOError as ioe:
            print(ioe.args)
    return True

#-------------------------------------
#---Zotero附件同步程序的主要步骤----
#-------------------------------------
def mainSteps(start_args):
    print(start_args)
    zotero_storage = None
    cloud_dir = None
    user_home = os.path.expanduser('~')
    settings_filename = os.path.join(user_home,'ZoteroAttachmentsSyncSettings.txt')
    
    # step 1: 创建或者读取设置文件
    # 如果传入了参数，则将参数写入配置文件
    if start_args and (isinstance(start_args,tuple) or isinstance(start_args, list)) and 3==len(start_args):
        zotero_storage = start_args[1]
        cloud_dir = start_args[2]
        with open(settings_filename,'w') as wf:
            wf.write(zotero_storage+'\n')
            wf.write(cloud_dir)
    elif not start_args:
        # 如果没有传入产生，则读取配置文件
        if not os.path.exists(settings_filename):
            return
        with open(settings_filename,'r') as wf:
            lines = wf.readlines()
            zotero_storage = lines[0].replace('\n','')
            cloud_dir = lines[1]
    
    #如果两个参数不是目录，直接返回
    if not os.path.isdir(zotero_storage) or not os.path.isdir(cloud_dir):
        return

    # step 2: 复制zotero.sqlite数据库至云目录，并在数据库中创建相关视图
    parent_dir = os.path.dirname(zotero_storage)
    zotero_sqlite_path = os.path.join(parent_dir, 'zotero.sqlite')
    zotero_sqlite_path_inCloudDir = os.path.join(cloud_dir, 'zotero.sqlite')

    try:
        # （1）：复制数据
        if os.path.exists(zotero_sqlite_path) and os.path.exists(zotero_sqlite_path_inCloudDir):
            # 如果两个目录下都存在zotero.sqlite，则以新的版本覆盖旧的
            copyNewEachOther(zotero_sqlite_path, zotero_sqlite_path_inCloudDir)
        else:
            # 否则，将storage目录中的zotero.sqlite复制到云同步目录
            shutil.copy2(zotero_sqlite_path, cloud_dir)
        # （2）：在zotero.sqlite数据库中创建相关的视图
        checkOrCreateViews(zotero_sqlite_path_inCloudDir)
    except IOError as e:
        print(e.strerror)

    # step 3: 删除云同步目录下的所有不必保留的文件
    records_lst = listAllNutsFiles(cloud_dir)
    keeped_lst= fetchAllKeepedAttachments(zotero_sqlite_path_inCloudDir)
    if not keeped_lst:
        return
    keeped_files = [f for k, f in keeped_lst]
    for fname in records_lst:
        if fname not in keeped_files:
            try:
                os.remove(os.path.join(cloud_dir, fname))
            except IOError as ioe:
                print(ioe.args)

    
    # step 4: storage目录下的文件和Cloud目录下文件互相拷贝（新的文件覆盖旧的文件）
    interleavedCopy(zotero_storage, cloud_dir, keeped_lst)


#-------------------------------------
#---windows服务类，运行时调用上面的mainSteps函数----
#-------------------------------------
class PythonService(win32serviceutil.ServiceFramework): 

    _svc_name_ = "ALuogeZoteroSync"   #  服务名
    _svc_display_name_ = "Zotero附件同步至坚果云目录服务"   # 服务在windows系统中显示的名称
    _svc_description_ = "同步Zotero的题录的附件文件"   #服务的描述

    def __init__(self, args): 
        win32serviceutil.ServiceFramework.__init__(self, args) 
        self.hWaitStop = win32event.CreateEvent(None, 0, 0, None)
        self.run = True
        self.myargs = args  # 调用start命令时，后面传入的参数在这里接收
        
    def _executeTask(self):
        mainSteps(self.myargs)
        
    def SvcDoRun(self):
        while self.run:
            # 已经运行
            self._executeTask()
            time.sleep(4)   #推迟调用线程的运行4秒
                
    def SvcStop(self): 
        # 服务已经停止
        self.ReportServiceStatus(win32service.SERVICE_STOP_PENDING) 
        win32event.SetEvent(self.hWaitStop) 
        self.run = False

if __name__ == "__main__":

    # args = ['ALuogeZoteroSync', r"C:\Users\ABU\Zotero\storage", r'F:\zotero']
    # mainSteps(args)
    
    print(sys.argv)
    if len(sys.argv) == 1:
        try:
            evtsrc_dll = os.path.abspath(servicemanager.__file__)
            servicemanager.PrepareToHostSingle(PythonService)
            servicemanager.Initialize('PythonService', evtsrc_dll)
            servicemanager.StartServiceCtrlDispatcher()

        except win32service.error as details:
            if details[0] == winerror.ERROR_FAILED_SERVICE_CONTROLLER_CONNECT:
                win32serviceutil.usage()
    else:
        win32serviceutil.HandleCommandLine(PythonService)
        